-- MedAssist Clinic Management System
-- Existing sample data from the supplied database
USE `medassets`;

INSERT INTO `appointment` (`AppointmentID`, `PatientID`, `DoctorID`, `AppointmentDate`, `AppointmentTime`, `Reason`, `Status`) VALUES
(1, 1, 1, '2026-01-05', '09:00:00', 'General Check-up', 'Completed'),
(2, 2, 2, '2026-01-05', '09:30:00', 'Diabetes Review', 'Completed'),
(3, 3, 3, '2026-01-06', '10:00:00', 'Skin Rash', 'Completed'),
(4, 4, 4, '2026-01-06', '10:30:00', 'Eczema', 'Completed'),
(5, 5, 5, '2026-01-07', '09:00:00', 'Back Pain', 'Completed'),
(6, 6, 6, '2026-01-07', '09:30:00', 'Knee Injury', 'Completed'),
(7, 7, 7, '2026-01-08', '10:00:00', 'Chest Pain', 'Completed'),
(8, 8, 8, '2026-01-08', '10:30:00', 'Heart Check', 'Completed'),
(9, 9, 9, '2026-01-09', '09:00:00', 'Child Fever', 'Completed'),
(10, 10, 10, '2026-01-09', '09:30:00', 'Vaccination', 'Completed'),
(11, 11, 1, '2026-01-10', '09:00:00', 'Headache', 'Completed'),
(12, 12, 2, '2026-01-10', '09:30:00', 'Blood Pressure', 'Completed'),
(13, 13, 3, '2026-01-11', '10:00:00', 'Acne', 'Completed'),
(14, 14, 4, '2026-01-11', '10:30:00', 'Skin Allergy', 'Completed'),
(15, 15, 5, '2026-01-12', '09:00:00', 'Shoulder Pain', 'Completed'),
(16, 16, 6, '2026-01-12', '09:30:00', 'Sports Injury', 'Completed'),
(17, 17, 7, '2026-01-13', '10:00:00', 'Heart Review', 'Completed'),
(18, 18, 8, '2026-01-13', '10:30:00', 'ECG Follow-up', 'Completed'),
(19, 19, 9, '2026-01-14', '09:00:00', 'Child Cough', 'Completed'),
(20, 20, 10, '2026-01-14', '09:30:00', 'Routine Check', 'Completed'),
(21, 21, 1, '2026-01-15', '09:00:00', 'Flu', 'Booked'),
(22, 22, 2, '2026-01-15', '09:30:00', 'Diabetes Review', 'Booked'),
(23, 23, 3, '2026-01-16', '10:00:00', 'Psoriasis', 'Booked'),
(24, 24, 4, '2026-01-16', '10:30:00', 'Skin Infection', 'Booked'),
(25, 25, 5, '2026-01-17', '09:00:00', 'Neck Pain', 'Booked'),
(26, 1, 6, '2026-01-17', '09:30:00', 'Follow-up', 'Booked'),
(27, 2, 7, '2026-01-18', '10:00:00', 'Chest Pain', 'Booked'),
(28, 3, 8, '2026-01-18', '10:30:00', 'Heart Check', 'Booked'),
(29, 4, 9, '2026-01-19', '09:00:00', 'Child Check', 'Booked'),
(30, 5, 10, '2026-01-19', '09:30:00', 'Vaccination', 'Booked'),
(31, 6, 1, '2026-01-20', '09:00:00', 'Asthma', 'Booked'),
(32, 7, 2, '2026-01-20', '09:30:00', 'Cold', 'Booked'),
(33, 8, 3, '2026-01-21', '10:00:00', 'Skin Mole', 'Booked'),
(34, 9, 4, '2026-01-21', '10:30:00', 'Dermatitis', 'Booked'),
(35, 10, 5, '2026-01-22', '09:00:00', 'Joint Pain', 'Booked'),
(36, 11, 6, '2026-01-22', '09:30:00', 'Muscle Pain', 'Booked'),
(37, 12, 7, '2026-01-23', '10:00:00', 'Blood Pressure', 'Booked'),
(38, 13, 8, '2026-01-23', '10:30:00', 'Heart Review', 'Booked'),
(39, 14, 9, '2026-01-24', '09:00:00', 'Child Fever', 'Booked'),
(40, 15, 10, '2026-01-24', '09:30:00', 'Routine Check', 'Booked'),
(43, 32, 1, '2026-08-15', '10:00:00', 'General consultation', 'Scheduled'),
(45, 7, 2, '2026-08-13', '13:00:00', 'Routine Check', 'Booked');

INSERT INTO `auditlog` (`AuditID`, `TableName`, `RecordID`, `ActionType`, `ActionDescription`, `ActionDateTime`) VALUES
(1, 'patient', 27, 'INSERT', 'New patient added: Sarah Wilson', '2026-08-08 15:44:55'),
(6, 'patient', 32, 'INSERT', 'New patient added: NIGIST ESHETU', '2026-08-08 21:47:40'),
(7, 'patient', 33, 'INSERT', 'New patient added: NIGIST ESHETU', '2026-08-08 21:51:19'),
(8, 'patient', 33, 'DELETE', 'Patient deleted: NIGIST ESHETU', '2026-08-09 13:44:04'),
(9, 'patient', 34, 'INSERT', 'New patient added: kidist Eshetu', '2026-08-09 16:14:16'),
(10, 'patient', 1, 'UPDATE', 'Patient updated: Alice Walker', '2026-08-09 16:21:16'),
(11, 'patient', 1, 'UPDATE', 'Patient updated: Alice Walker', '2026-08-09 16:22:04'),
(12, 'patient', 26, 'DELETE', 'Patient deleted: John Smith', '2026-08-10 14:35:15');

INSERT INTO `department` (`DepartmentID`, `DepartmentName`, `Location`) VALUES
(1, 'General Medicine', 'Building A'),
(2, 'Dermatology', 'Building B'),
(3, 'Physiotherapy', 'Building C'),
(4, 'Cardiology', 'Building D'),
(5, 'Paediatrics', 'Building E'),
(6, 'Endocrinology', 'Building  E');

INSERT INTO `doctor` (`DoctorID`, `DepartmentID`, `FirstName`, `LastName`, `Specialisation`, `Phone`, `Email`) VALUES
(1, 1, 'Nigist', 'Eshetu', 'GeneralPhysician', '07944822304', 'Nigist.Eshetu@medassist.co.uk'),
(2, 1, 'kidist', 'Brown', 'General Physician', '07944822305', 'kidist.brown@medassist.co.uk'),
(3, 2, 'Alemitu', 'Wilson', 'Dermatologist', '07944822306', 'Alemitu.wilson@medassist.co.uk'),
(4, 2, 'Dawit', 'Taylor', 'Dermatologist', '07944822307', 'Dawit.taylor@medassist.co.uk'),
(5, 3, 'Michael', 'Johnson', 'Physiotherapist', '07944822308', 'Michael.johnson@medassist.co.uk'),
(6, 3, 'Jambo', 'White', 'Physiotherapist', '07944822309', 'Jambo.white@medassist.co.uk'),
(7, 4, 'Daniel', 'Evans', 'Cardiologist', '07944822301', 'daniel.evans@medassist.co.uk'),
(8, 4, 'Sophia', 'Hall', 'Cardiologist', '07944822302', 'sophia.hall@medassist.co.uk'),
(9, 5, 'Jemal', 'Green', 'Paediatrician', '07944822303', 'Jemal.green@medassist.co.uk'),
(10, 5, 'Sara', 'Lewis', 'Paediatrician', '07944822310', 'Sara.lewis@medassist.co.uk'),
(11, 4, 'KALEB', 'ABEGAZ', 'General Practitioner', '07944822399', 'KALEBABEGAZ@gmail.com');

INSERT INTO `feedback` (`FeedbackID`, `PatientID`, `AppointmentID`, `Rating`, `Comments`, `FeedbackDate`) VALUES
(1, 1, 1, 5, 'Excellent service.', '2026-01-05'),
(2, 2, 2, 4, 'Very helpful doctor.', '2026-01-05'),
(3, 3, 3, 5, 'Professional consultation.', '2026-01-06'),
(4, 4, 4, 4, 'Satisfied with treatment.', '2026-01-06'),
(5, 5, 5, 5, 'Friendly staff.', '2026-01-07'),
(6, 6, 6, 4, 'Quick appointment.', '2026-01-07'),
(7, 7, 7, 5, 'Very caring doctor.', '2026-01-08'),
(8, 8, 8, 5, 'Excellent experience.', '2026-01-08'),
(9, 9, 9, 4, 'Good service.', '2026-01-09'),
(10, 10, 10, 5, 'Highly recommended.', '2026-01-09'),
(11, 11, 11, 5, 'Great diagnosis.', '2026-01-10'),
(12, 12, 12, 4, 'Very professional.', '2026-01-10'),
(13, 13, 13, 5, 'Helpful advice.', '2026-01-11'),
(14, 14, 14, 4, 'Good communication.', '2026-01-11'),
(15, 15, 15, 5, 'Excellent physiotherapy.', '2026-01-12'),
(16, 16, 16, 4, 'Very satisfied.', '2026-01-12'),
(17, 17, 17, 5, 'Outstanding care.', '2026-01-13'),
(18, 18, 18, 4, 'Clear explanations.', '2026-01-13'),
(19, 19, 19, 5, 'Doctor was kind.', '2026-01-14'),
(20, 20, 20, 5, 'Fantastic clinic.', '2026-01-14');

INSERT INTO `patient` (`PatientID`, `FirstName`, `LastName`, `Gender`, `DOB`, `Phone`, `Email`, `Address`, `MedicalHistory`) VALUES
(1, 'Alice', 'Walker', 'Female', '1995-02-14', '07994483301', 'emily.johnson.new@example.com', 'Manchester, UK', 'Asthma'),
(2, 'Ben', 'Harris', 'Male', '1988-06-21', '07200000002', 'ben.harris@email.com', '15 Queen St, London', 'Diabetes'),
(3, 'Charlotte', 'Young', 'Female', '1992-04-08', '07200000003', 'charlotte@email.com', '8 Hill Rd, London', 'None'),
(4, 'Daniel', 'King', 'Male', '1985-01-12', '07200000004', 'daniel@email.com', '5 River Rd', 'Hypertension'),
(5, 'Emma', 'Scott', 'Female', '1999-10-05', '07200000005', 'emma@email.com', '17 Green Rd', 'None'),
(6, 'Freddie', 'Hill', 'Male', '1994-03-17', '07200000006', 'freddie@email.com', '20 Park Ave', 'Asthma'),
(7, 'Grace', 'Adams', 'Female', '1990-07-18', '07200000007', 'grace@email.com', '25 London Rd', 'None'),
(8, 'Harry', 'Baker', 'Male', '1986-12-11', '07200000008', 'harry@email.com', '9 Station Rd', 'Allergy'),
(9, 'Isabella', 'Clark', 'Female', '2001-05-30', '07200000009', 'isabella@email.com', '33 Church Rd', 'None'),
(10, 'Jack', 'Cooper', 'Male', '1993-09-09', '07200000010', 'jack@email.com', '41 High St', 'Diabetes'),
(11, 'Katie', 'Edwards', 'Female', '1998-11-20', '07200000011', 'katie@email.com', '18 King Rd', 'None'),
(12, 'Luke', 'Foster', 'Male', '1982-04-03', '07200000012', 'luke@email.com', '7 Queen Rd', 'Hypertension'),
(13, 'Mia', 'Gray', 'Female', '1996-06-28', '07200000013', 'mia@email.com', '11 Green Rd', 'Migraine'),
(14, 'Noah', 'Howard', 'Male', '1997-07-07', '07200000014', 'noah@email.com', '10 Park Rd', 'None'),
(15, 'Olivia', 'James', 'Female', '1989-08-22', '07200000015', 'olivia@email.com', '55 Queen Rd', 'Asthma'),
(16, 'Peter', 'Kelly', 'Male', '1984-01-14', '07200000016', 'peter@email.com', '2 High St', 'Diabetes'),
(17, 'Ruby', 'Lee', 'Female', '2000-03-09', '07200000017', 'ruby@email.com', '44 River Rd', 'None'),
(18, 'Samuel', 'Moore', 'Male', '1991-02-25', '07200000018', 'sam@email.com', '9 Oak Rd', 'None'),
(19, 'Tina', 'Nelson', 'Female', '1987-10-12', '07200000019', 'tina@email.com', '17 Wood Rd', 'Hypertension'),
(20, 'Victor', 'Owens', 'Male', '1995-12-15', '07200000020', 'victor@email.com', '30 Hill Rd', 'None'),
(21, 'William', 'Price', 'Male', '1998-09-01', '07200000021', 'will@email.com', '16 Elm Rd', 'None'),
(22, 'Zoe', 'Roberts', 'Female', '1993-11-13', '07200000022', 'zoe@email.com', '80 Church Rd', 'Migraine'),
(23, 'Aaron', 'Turner', 'Male', '1996-05-16', '07200000023', 'aaron@email.com', '7 River St', 'None'),
(24, 'Bella', 'Ward', 'Female', '1994-02-19', '07200000024', 'bella@email.com', '18 King Ave', 'Asthma'),
(25, 'Chris', 'Wood', 'Male', '1983-08-27', '07200000025', 'chris@email.com', '65 Queen Ave', 'Diabetes'),
(27, 'Sarah', 'Wilson', 'Female', '1988-04-20', '07999888777', 'sarah@email.com', 'London', NULL),
(32, 'NIGIST', 'ESHETU', 'Female', '1990-05-15', '07900123456', 'nigist.eshetu@email.com', 'London, UK', 'No known medical conditions'),
(34, 'kidist', 'Eshetu', 'Female', '2026-08-09', '07944822304', 'nigisteshetu0@gmail.com', '30 exerted road', 'OCD');

INSERT INTO `treatment` (`TreatmentID`, `AppointmentID`, `Diagnosis`, `Prescription`, `Notes`) VALUES
(1, 1, 'Healthy', 'Vitamin D', 'Routine check'),
(2, 2, 'Type 2 Diabetes', 'Metformin', 'Review after 3 months'),
(3, 3, 'Dermatitis', 'Hydrocortisone Cream', 'Avoid irritants'),
(4, 4, 'Eczema', 'Moisturiser', 'Daily application'),
(5, 5, 'Muscle Strain', 'Ibuprofen', 'Physiotherapy advised'),
(6, 6, 'Knee Sprain', 'Painkillers', 'Rest for 2 weeks'),
(7, 7, 'Chest Pain', 'ECG Required', 'Cardiology referral'),
(8, 8, 'Normal ECG', 'None', 'Annual review'),
(9, 9, 'Viral Infection', 'Paracetamol', 'Rest'),
(10, 10, 'Vaccination Complete', 'None', 'Next vaccine in 12 months'),
(11, 11, 'Migraine', 'Sumatriptan', 'Reduce stress'),
(12, 12, 'Hypertension', 'Amlodipine', 'Monitor BP'),
(13, 13, 'Acne', 'Benzoyl Peroxide', 'Skin care advice'),
(14, 14, 'Allergic Rash', 'Antihistamine', 'Avoid allergens'),
(15, 15, 'Frozen Shoulder', 'Pain Relief', 'Exercise'),
(16, 16, 'Sports Injury', 'Ibuprofen', 'Physiotherapy'),
(17, 17, 'Heart Disease', 'Aspirin', 'Lifestyle changes'),
(18, 18, 'Stable Condition', 'Continue Medication', 'Follow-up'),
(19, 19, 'Cold', 'Paracetamol', 'Fluids'),
(20, 20, 'Healthy', 'None', 'Routine review'),
(21, 21, 'Flu', 'Paracetamol', 'Rest'),
(22, 22, 'Diabetes', 'Metformin', 'Diet advice'),
(23, 23, 'Psoriasis', 'Steroid Cream', 'Follow-up'),
(24, 24, 'Skin Infection', 'Antibiotics', 'Complete course'),
(25, 25, 'Neck Strain', 'Ibuprofen', 'Rest'),
(26, 26, 'General Review', 'None', 'No issues'),
(27, 27, 'Angina', 'Nitroglycerin', 'Refer specialist'),
(28, 28, 'Normal', 'None', 'Healthy'),
(29, 29, 'Healthy Child', 'None', 'Routine'),
(30, 30, 'Vaccination', 'None', 'Completed'),
(31, 31, 'Asthma', 'Inhaler', 'Monitor'),
(32, 32, 'Common Cold', 'Paracetamol', 'Hydration'),
(33, 33, 'Benign Mole', 'None', 'Observe'),
(34, 34, 'Dermatitis', 'Cream', 'Skin care'),
(35, 35, 'Joint Pain', 'Ibuprofen', 'Exercise'),
(36, 36, 'Muscle Strain', 'Pain Relief', 'Rest'),
(37, 37, 'Hypertension', 'Medication', 'Review'),
(38, 38, 'Heart Murmur', 'Further Tests', 'Referral'),
(39, 39, 'Fever', 'Paracetamol', 'Monitor'),
(40, 40, 'Healthy', 'None', 'Routine'),
(41, 43, 'Initial consultation', 'To be determined', 'Initial patient assessment');
